package com.inf.service;

import com.inf.utils.PhoenixUtil;
import com.inf.utils.PropertiesUtil;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

/**
 * Created by zhangbn on 2018/11/29.
 */
@Service
public class FrequencyStatsService {

    Logger log = LoggerFactory.getLogger(FrequencyStatsService.class);

    /**
     * 频次统计
     *
     * @param begin
     * @param end
     * @param cameraIds
     * @return
     */
    public Map<String, Object> frequencyStats(String begin, String end, String cameraIds) {
        Map<String, Object> resultMap = new HashMap<>();
        Integer total = 0;
        List<Map<String, Object>> topNList = new ArrayList<>();
        List<String> cameraIdList = null;
        if (StringUtils.isNotEmpty(cameraIds)) {
            cameraIdList = Arrays.asList(cameraIds.split(","));
        }
        String sql1 = "select count(distinct substr(\"rowkey\",13,32)) as total from \"bigdata\".\"person\" where \"rowkey\" between '" + begin + "' and '" + end + "'";
        String sql2 = "select substr(\"rowkey\",13,32) uuid,count(*) nums from \"bigdata\".\"person\" where \"rowkey\" between '" + begin + "' and '" + end + "' group by uuid order by " +
                "nums desc limit " + PropertiesUtil.getProperty("personface.frequencyStats.topN", "10");
        Connection conn = PhoenixUtil.getConnection();
        Statement statement = null;
        ResultSet rst = null;
        try {
            statement = conn.createStatement();
            rst = statement.executeQuery(sql1);
            while (rst.next()) {
                total = rst.getInt(1);
                log.info("show: 总人数total=" + total);
            }
            rst = statement.executeQuery(sql2);
            while (rst.next()) {
                Map<String, Object> mp = new HashMap<>();
                mp.put("uuid", rst.getString(1));
                mp.put("nums", rst.getInt(2));
                topNList.add(mp);
            }
            log.info("show: 频次topNList=" + topNList);
            rst.close();
            statement.close();
            resultMap.put("total", total);
            resultMap.put("list", topNList);
        } catch (Exception e) {
            log.error("频次统计查询出错：" + e.toString());
        } finally {
            try {
                statement.close();
                rst.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return resultMap;
    }


    /**
     * 频次统计下钻
     *
     * @param begin
     * @param end
     * @param cameraIds
     * @param uuid
     * @return
     */
    public List<Map<String, Object>> frequencyStatsDetail(String begin, String end, String cameraIds, String uuid) {
        List<Map<String, Object>> topNList = new ArrayList<>();
        List<String> cameraIdList = null;
        if (StringUtils.isNotEmpty(cameraIds)) {
            cameraIdList = Arrays.asList(cameraIds.split(","));
        }
        String sql = "select substr(\"rowkey\",13,32) uuid,\"pass_id\", \"pass_time\",\"camera_id\",\"face_img_url\" from \"bigdata\".\"person\" where \"rowkey\" between " +
                "'" + begin + "' and '" + end + "' and uuid='" + uuid + "' limit " + PropertiesUtil.getProperty("personface.frequencyStats.topN", "10");
        Connection conn = PhoenixUtil.getConnection();
        Statement statement = null;
        ResultSet rst = null;
        try {
            statement = conn.createStatement();
            rst = statement.executeQuery(sql);
            while (rst.next()) {
                Map<String, Object> mp = new HashMap<>();
                mp.put("uuid", rst.getString(1));
                mp.put("nums", rst.getInt(2));
                topNList.add(mp);
            }
            log.info("show: 频次明细topNList=" + topNList);
            rst.close();
            statement.close();
        } catch (Exception e) {
            log.error("频次明细查询出错：" + e.toString());
        } finally {
            try {
                statement.close();
                rst.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return topNList;
    }

}
